-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,plpgsq函数，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop function if exists add;
drop function if exists increment;
drop function if exists dup;
drop function if exists dup;
drop function if exists dup_2;
drop function if exists check_password;
drop type if exists dup_result;
drop table if exists emp_rec;
drop table if exists tt;
drop function if exists exception_proc(int,char);
drop function if exists func_add_sql2(integer,integer);
create extension gms_output;
--plpgsql
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;
set behavior_compat_options='skip_insert_gs_source,select_into_return_null';
--创建表
drop table if exists tt_cache;
create table tt_cache(
id int,
col_numeric numeric
);
insert into tt_cache values(
generate_series(1,20)%4,
generate_series(1,20)%3
);
select count(*) from tt_cache;
--case1
create or replace function add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
/
IMMUTABLE
result_cache
RETURNS NULL ON NULL INPUT;
select add(100,12);
select add(cast(id as int),cast(col_numeric as int)) from tt_cache;
select add(add(cast(id as int),cast(col_numeric as int)),add(cast(id as int),cast(col_numeric as int))) from tt_cache;
select add(12,100) from tt_cache;
--case3
create or replace function increment(i integer) RETURNS integer result_cache AS
$$
BEGIN
raise notice 'NOTICE: excute function...';
RETURN i + 1;
END;
$$ LANGUAGE plpgsql stable;
/
select increment(12);
select increment(id) from tt_cache;
select increment(increment(id)) from tt_cache;
select increment(12) from tt_cache;
--case4
create or replace function dup(in int, out f1 int, out f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
/
SELECT * FROM dup(42);
select dup(12);
select dup(id) from tt_cache;
select dup(12) from tt_cache;
--case5
CREATE or replace TYPE dup_result AS object (f1 int, f2 text);
drop function if exists dup;
create or replace function dup(int) RETURNS dup_result
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL result_cache immutable;
/
select dup_result(12,'qwer');
--case6
create or replace function dup_2(int) RETURNS TABLE(f1 int, f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL result_cache stable;
/
SELECT * FROM dup_2(42);
--case7
drop table if exists pwds;
create table pwds(username varchar(30),pwd varchar(30));
insert into pwds values('usr'||generate_series(1,10),'mm123');
create or replace function check_password(uname varchar, pass varchar)
RETURNS BOOLEAN result_cache AS $$
DECLARE passed BOOLEAN;
BEGIN
raise notice 'NOTICE: excute function...';
SELECT (pwd = $2) INTO passed
FROM pwds
WHERE username = $1;
RETURN passed;
END;
$$ LANGUAGE plpgsql;
/
select check_password('usr1','mm123');
--缺陷
--case10
--case10-1
--提示信息不准确，已提交缺陷7478
create or replace function exception_proc(i1 in int,col out char) returns int as $$
declare
x int default 0;
BEGIN
raise notice 'NOTICE: excute function...';
return x;
END;
$$ language plpgsql stable result_cache;
/
--case10-2
--7479
drop table if exists emp_rec;
CREATE TABLE emp_rec
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);
INSERT INTO emp_rec VALUES (7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO emp_rec VALUES (7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO emp_rec VALUES (7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO emp_rec VALUES (7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO emp_rec VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO emp_rec VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO emp_rec VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO emp_rec VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp_rec VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO emp_rec VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO emp_rec VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO emp_rec VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO emp_rec VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp_rec VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
SELECT distinct mgr, comm FROM emp_rec WHERE comm is not null order by nvl(mgr,0);
--case10-3 7482
drop table if exists tt;
create table tt(id int,col_number number,col_var varchar(50));
select count(id) from (select * from tt order by id) order by id;
--case10-4 7483
CREATE or replace FUNCTION func_add_sql2(num1 integer, num2 integer) RETURN integer result_cache result_cache
AS
BEGIN
RETURN num1 + num2;
END;
/
--teardown
drop function if exists add;
drop function if exists increment;
drop function if exists dup;
drop function if exists dup;
drop function if exists dup_2;
drop function if exists check_password;
drop type if exists dup_result;
drop table if exists emp_rec;
drop table if exists tt;
drop function if exists exception_proc(int,char);
drop function if exists func_add_sql2(integer,integer);
